package com.hfjyz.registration.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.hfjyz.registration.command.OfficeListCommand;
import com.hfjyz.registration.model.City;
import com.hfjyz.registration.model.Depart;
import com.hfjyz.registration.model.Exam;
import com.hfjyz.registration.model.Office;

public class OfficeDaoImpl extends BaseDaoImpl<Office, Integer> implements
		OfficeDao {

	protected static String HQL_FOR_FIND_OFFICE = " select " + "     {O.*}, "
			+ "     {C.*}, " + "     {D.*}, " + "     {E.*} " + " from "
			+ "     office O " + "     left join exam E on E.id = O.examId "
			+ "     left join depart D on D.id=O.departId "
			+ "     left join city C on C.id = D.cityId " + " where "
			+ "     1 = 1 ";

	protected OfficeDaoImpl() {
		super(Office.class);
	}

	@Override
	public List<Office> listAll() throws Exception {
		// String hql = "from Office O, Depart D, City C "
		// + " where O.departId=D.id and C.id = D.cityId ";
		// List<?> list = getHibernateTemplate().find(hql);
		//
		// List<Office> offices = new ArrayList<Office>();
		// for (Object o : list) {
		// Object[] objects = (Object[]) o;
		// final Office office = (Office) objects[0];
		// office.setDepartName(((Depart) objects[1]).getName());
		// office.setCityName(((City) objects[2]).getName());
		// final String sql = "select count(*) as applyCount, "
		// + " sum(case when state = 2 then 1 else 0 end) as validateCount "
		// + " from Apply as apply where apply.officeid = ? ";
		// Object[] apply = (Object[]) getHibernateTemplate().execute(
		// new HibernateCallback() {
		// public Object doInHibernate(Session session)
		// throws HibernateException, SQLException {
		// return (Object[]) session.createSQLQuery(sql)
		// .setParameter(0, office.getId())
		// .uniqueResult();
		// }
		// });
		// if (apply == null) {
		// apply = new Integer[2];
		// apply[0] = apply[1] = 0;
		// }
		// int validateCount = apply[1] == null ? 0 : ((Number) apply[1])
		// .intValue();
		// office.setApplyCount(apply[0] == null ? 0 : ((Number) apply[0])
		// .intValue());
		// office.setValidataCount(validateCount);
		// office.setScale(validateCount / office.getRecruits());
		// offices.add(office);
		// }
		List<Office> offices = super.listAll();
		getOfficeExtInfo(offices);
		getOfficeApplyInfo(offices);
		return offices;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Office> listByExam(Exam exam) throws Exception {
		List<Office> offices = getHibernateTemplate().find(
				"from Office where examId = ? ", exam.getId());
		getOfficeExtInfo(offices);
		getOfficeApplyInfo(offices);
		return offices;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Office> getOfficeById(String examId) throws Exception {
		List<Office> offices = getHibernateTemplate().find(
				"from Office where examId = ? ", Integer.valueOf(examId));
		return offices;
	}

	private void getOfficeExtInfo(List<Office> offices) {
		if (offices == null || offices.isEmpty()) {
			return;
		}
		Map<Integer, City> tempCity = new HashMap<Integer, City>();
		Map<Integer, Depart> tempDepart = new HashMap<Integer, Depart>();
		Map<Integer, Exam> tempExam = new HashMap<Integer, Exam>();
		for (final Office office : offices) {
			if (office.getExamId() != null) {
				Exam e = tempExam.get(office.getExamId());
				if (e == null) {
					e = (Exam) getHibernateTemplate().get(Exam.class,
							office.getExamId());
					tempExam.put(office.getExamId(), e);
				}
				if (e != null) {
					office.setExamName(e.getName());
				}
			}
			if (office.getDepartId() != null) {
				Depart d = tempDepart.get(office.getDepartId());
				if (d == null) {
					d = (Depart) getHibernateTemplate().get(Depart.class,
							office.getDepartId());
					tempDepart.put(office.getDepartId(), d);
				}
				if (d != null) {
					office.setDepartName(d.getName());
					if (d.getCityId() != null) {
						City c = tempCity.get(d.getCityId());
						if (c == null) {
							c = (City) getHibernateTemplate().get(City.class,
									office.getDepartId());
							tempCity.put(d.getCityId(), c);

						}
						if (c != null) {
							office.setCityName(c.getName());
						}
					}
				}

			}
		}
	}

	private void getOfficeApplyInfo(List<Office> offices) {
		for (final Office office : offices) {
			final String sql = "select count(*) as applyCount, "
					+ " sum(case when apply.state = 2 then 1 else 0 end) as validateCount, "
					+ " sum(case when oi.orderState = 1 then 1 else 0 end) as paidCount "
					+ " from Apply as apply "
					+ " left join OrderInfo as oi on apply.userid = oi.userId and apply.examId = oi.examId and oi.deleteFlg = 0"
					+ " where apply.officeid = ? ";
			Object[] apply = (Object[]) getHibernateTemplate().execute(
					new HibernateCallback() {
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							return (Object[]) session.createSQLQuery(sql)
									.setParameter(0, office.getId())
									.uniqueResult();
						}
					});
			if (apply == null) {
				apply = new Integer[3];
				apply[0] = apply[1] = apply[2] = 0;
			}
			int validateCount = apply[1] == null ? 0 : ((Number) apply[1])
					.intValue();
			office.setApplyCount(apply[0] == null ? 0 : ((Number) apply[0])
					.intValue());
			office.setValidataCount(validateCount);
			office.setPaidCount(apply[2] == null ? 0 : ((Number)apply[2]).intValue());
			office.setScale(validateCount / office.getRecruits());
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Office> search(final OfficeListCommand cmd) throws Exception {
		return (List<Office>) getHibernateTemplate().execute(
				new HibernateCallback() {

					@SuppressWarnings("rawtypes")
					@Override
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = HQL_FOR_FIND_OFFICE;

						List<Object> paramList = new ArrayList<Object>();
						if (cmd.getExamId() != null && cmd.getExamId() >= 0) {
							hql += "and E.id=? ";
							paramList.add(cmd.getExamId());
						}
						if (cmd.getDepartId() != null && cmd.getDepartId() >= 0) {
							hql += "and D.id=? ";
							paramList.add(cmd.getDepartId());
						}
						if (cmd.getName() != null
								&& cmd.getName().trim().length() > 0) {
							hql += "and O.name LIKE ? ";
							paramList.add(getLikeStr(cmd.getName().trim()));
						}
						hql += " ORDER BY E.id, D.id, O.code ";
						SQLQuery sq = session.createSQLQuery(hql);
						sq.addEntity("O", Office.class);
						sq.addEntity("D", Depart.class);
						sq.addEntity("C", City.class);
						sq.addEntity("E", Exam.class);
						sq.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
						for (int i = 0; i < paramList.size(); i++) {
							sq.setParameter(i, paramList.get(i));
						}
						List<Office> offices = new ArrayList<Office>();
						List<?> list = sq.list();
						for (Object o : list) {
							Map map = (Map) o;
							Office office = (Office) map.get("O");
							Depart dept = (Depart) map.get("D");
							City city = (City) map.get("C");
							Exam exam = (Exam) map.get("E");
							if (dept != null) {
								office.setDepartName(dept.getName());
							}
							if (city != null) {
								office.setCityName(city.getName());
							}
							if (exam != null) {
								office.setExamId(exam.getId());
								office.setExamName(exam.getName());
							}
							offices.add(office);
						}
						return offices;
					}
				});
	}

	private String getLikeStr(String target) {
		if (target == null) {
			return null;
		}
		return "%" + target.replaceAll("%", "%%") + "%";
	}
}
